/**
  * This Procedure Closes the Sort Releases if their all the wafers gets the Binning Information.
  *
  */


ALTER PROCEDURE sp_close_sort_releases_by_sort_data_entry ( @sort_lot_id INT )
AS
BEGIN

	DECLARE @wafer_store_lot_id INT
	DECLARE @sort_process_step_id INT
	DECLARE @wafer_store_process_step_id INT
	DECLARE @lot_number VARCHAR(100)	

	DECLARE @sort_release_close_flag INT
	DECLARE @sort_rel_id INT
	DECLARE @wafer_ids VARCHAR(100)
	DECLARE @wafer_id VARCHAR(3)
	DECLARE @index INT

	DECLARE @wafers_with_bin_info_table TABLE ( Wafer_Id INT )


	--Collecting the Wafers, for which Bin Info received.
	INSERT INTO @wafers_with_bin_info_table SELECT DISTINCT WAFER_ID FROM INFORMATIVE_BINNING WHERE LOT_ID = @sort_lot_id

	--Getting the Sort Process Step Id and Lot Number for the Lot Id.
	SELECT @sort_process_step_id = PROCESSSTEP_ID,  @lot_number = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @sort_lot_id

	--Getting the Wafer Sort Process Step Id
	SELECT @wafer_store_process_step_id = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID( @sort_process_step_id ) WHERE Process_Type_Name = 'WAFER STORE'

	SELECT @wafer_store_lot_id = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @wafer_store_process_step_id AND LOT_NUMBER = @lot_number

		IF ( @wafer_store_lot_id IS NOT NULL AND @wafer_store_lot_id > 0 ) 
		BEGIN

			DECLARE @sortReleaseCursor CURSOR
			SET @sortReleaseCursor = CURSOR FOR SELECT SR.Sort_Rel_Id, SRL.Wafer_Id FROM SORT_RELEASE_TABLE SR, SORT_RELEASE_LOT_TABLE SRL WHERE SRL.SORT_REL_ID = SR.SORT_REL_ID and STATUS <> 'Cancelled' AND SRL.LOT_ID = @wafer_store_lot_id
			OPEN @sortReleaseCursor
			FETCH NEXT FROM @sortReleaseCursor INTO @sort_rel_id, @wafer_ids
			
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @sort_release_close_flag = 0 	--  0 means Need to Close Sort Release.
				
				SET @wafer_id = ''
				SET @index = 0				
				WHILE ( LEN(@wafer_ids) > 0  )
				BEGIN
					IF ( CHARINDEX ( ';', @wafer_ids, 1 ) > 0 ) 
					BEGIN
						SET @index = CHARINDEX ( ';', @wafer_ids, 1 )
						SET @wafer_id = SUBSTRING( @wafer_ids, 0, @index ) 
						SET @wafer_ids = SUBSTRING( @wafer_ids, @index+1, LEN( @wafer_ids ) )
						IF ( ( SELECT COUNT(*) FROM @wafers_with_bin_info_table WHERE Wafer_Id = @wafer_id ) = 0 )
						BEGIN
							SET @sort_release_close_flag = 1				
						END
					END	
				END
			
				--Close the Sort Release, as all of its wafer got the Binning Information.
				IF ( @sort_release_close_flag = 0 )
				BEGIN
					UPDATE SORT_RELEASE_TABLE SET STATUS = 'Closed' WHERE SORT_REL_ID = @sort_rel_id
				END

				SET @sort_rel_id = 0
				FETCH NEXT FROM @sortReleaseCursor INTO @sort_rel_id, @wafer_ids
			END
		END	

END